package com.prj.ufdm.demo.tutorial.j2se;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
/**
 * JDBC 链接测试
 * 1.jdbc connection就是一个连接数据库的TCP实例。
 * 2.connection是个长连接（MySQL默认8小时）的TCP。不像hTTP是个短连接的，每次都要3次握手才能建立。
 * 3.connection不是线程安全的！它在多线程环境中使用时,会导致数据操作的错乱,特别是有事务的情况。connection.commit()方法就是提交事务,你可以想象,在多线程环境中,线程A开启了事务,然后线程B却意外的commit,这该是个多么纠结的情况。
 * 4.多个线程同用一个connection会不会提高效率，减少多次连接的消耗？答：不会，因为connection里，每个方法都是synchronized，都执行了同步。
 * @author Administrator
 *
 */
public class JDBCConnectionTest {

	/* 公有属性 */
	private String url = "jdbc:mysql://192.168.128.128:3306/test";
	private String username = "root";
	private String password = "huyz";
	private String driverClassName = "com.mysql.jdbc.Driver";

	public static void main(String[] args) throws Exception {
		CountDownLatch connLatch = new CountDownLatch(1000);
		Lock lock = new ReentrantLock();
		JDBCConnectionTest test = new JDBCConnectionTest();
		Connection conn = test.getConn();
	    try {
	    	for(int i=0;i<100;i++) {
	    		final int j = i;
		    	new Thread(new Runnable() {
					@Override
					public void run() {
						lock.lock();
						PreparedStatement pstmt = null;
						try {
							System.out.println(Thread.currentThread().getName() + "开始执行.........");
						    String sql = "insert into A_STUDENTS (name,sex,age) values(?,?,?)";
//						    sql = "delete from A_STUDENTS";
					        pstmt = (PreparedStatement) conn.prepareStatement(sql);
					        pstmt.setString(1, j+"");
					        pstmt.setString(2, "1");
					        pstmt.setString(3, "26");
					        pstmt.executeUpdate();
					        System.out.println(Thread.currentThread().getName() + "执行成功.........");
						}
				        catch(Exception er) {
				        	System.out.println("第"+j+"发生异常："+er);
				        }
				        finally {
				        	 try {
								if(pstmt!=null) pstmt.close();
								lock.unlock();
								connLatch.countDown();
							} catch (SQLException e) {
								e.printStackTrace();
							}
				        }
					}
		    	}).start();
	    	}
	    	// 等到100个链接用完了关闭连接
	    	connLatch.await();
	    } catch (Exception e) {
	    	
	    }
	    finally {
	    	conn.close();
	    }
		
	}

	private Connection getConn() {
	    Connection conn = null;
	    try {
	        Class.forName(driverClassName); //classLoader,加载对应驱动
	        conn = (Connection) DriverManager.getConnection(url, username, password);
	    } catch (ClassNotFoundException e) {
	        e.printStackTrace();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return conn;
	}
	
}
